# -*- coding: utf-8 -*-
'''
Created on 2011/04/15

@author: pedro
'''
import sqlite3
import time


class RebidesBD():
    
    def __init__(self, nome):
        self.nome = nome
        self.conn = sqlite3.connect(nome)
        
    def criar_tabelas(self):
        '''
        criacao das tabelas da base de dados
        '''
        
        cursor = self.conn.cursor()
        
        cursor.execute("""CREATE TABLE IF NOT EXISTS Ano 
                       (Ano INTEGER PRIMARY KEY)
                       """)
        
        cursor.execute("""CREATE TABLE IF NOT EXISTS TipoEstabelecimento 
                       (NomeTipoEstabelecimento TEXT PRIMARY KEY)
                       """)
        
        cursor.execute("""CREATE TABLE IF NOT EXISTS TipoEstabelecimento_Ano
                       (
                       Ano INTEGER, 
                       NomeTipoEstabelecimento TEXT,
                       FOREIGN KEY(Ano) REFERENCES Ano,
                       FOREIGN KEY(NomeTipoEstabelecimento) REFERENCES TipoEstabelecimento
                       PRIMARY KEY (Ano, NomeTipoEstabelecimento))
                       """)
        
        cursor.execute("""CREATE TABLE IF NOT EXISTS Estabelecimento 
                       ( NomeEstabelecimento TEXT PRIMARY KEY,
                       NomeTipoEstabelecimento TEXT,
                       FOREIGN KEY(NomeTipoEstabelecimento) REFERENCES TipoEstabelecimento)
                       """)
        
        cursor.execute("""CREATE TABLE IF NOT EXISTS Grau 
                       ( NomeGrau TEXT PRIMARY KEY )
                       """)
        
        cursor.execute("""CREATE TABLE IF NOT EXISTS Curso
                       ( NomeCurso TEXT PRIMARY KEY )
                       """)
        
        cursor.execute("""CREATE TABLE IF NOT EXISTS Categoria 
                       ( NomeCategoria TEXT PRIMARY KEY )
                       """)
         
        cursor.execute("""CREATE TABLE IF NOT EXISTS Professor
                       ( NomeProfessor TEXT PRIMARY KEY,
                       CategoriaProfessor TEXT,
                       CodigoProfessor INTEGER,
                       FOREIGN KEY(CategoriaProfessor) REFERENCES Categoria)
                       """)
        cursor.execute("""CREATE TABLE IF NOT EXISTS ProfessorEstabelecimento
                        (
                       NomeEstabelecimento TEXT, 
                       NomeProfessor TEXT,
                       Ano INTEGER,
                       FOREIGN KEY(NomeEstabelecimento) REFERENCES Estabelecimento ,
                       FOREIGN KEY(NomeProfessor) REFERENCES Professor,
                       FOREIGN KEY(Ano) REFERENCES Ano,
                       PRIMARY KEY (NomeEstabelecimento, NomeProfessor, Ano))
                        """)
        cursor.execute("""CREATE TABLE IF NOT EXISTS GrauProfessor
                       (
                       NomeGrau TEXT, 
                       NomeProfessor TEXT,
                       Ano INTEGER,
                       CursoProfessor TEXT,
                       FOREIGN KEY(NomeGrau) REFERENCES Grau,
                       FOREIGN KEY(NomeProfessor) REFERENCES Professor,
                       FOREIGN KEY(CursoProfessor) REFERENCES Curso,
                       FOREIGN KEY(Ano) REFERENCES Ano,
                       PRIMARY KEY(NomeGrau, NomeProfessor,Ano, CursoProfessor))
                       """)
        cursor.close()
        pass
    
    
  
    
    def preencherGrauProfessor(self, prof):
        GRAU_PROF = 2
        NOME_PROF = 1
        ANO = 6
        CURSO_PROF = 3
        anoCompleto = 2000 + prof[ANO]
        cursor = self.conn.cursor()
        cmd = """INSERT INTO Grau(NomeGrau) values ('{0}')""".format(prof[GRAU_PROF])
        try:
            cursor.execute(cmd)
        except:
            print "erro grau", prof[GRAU_PROF]
            pass
        cmd = """INSERT INTO GrauProfessor( NomeGrau, NomeProfessor, Ano, CursoProfessor) 
                values ('{0}', '{1}', {2}, '{3}')""".format(prof[GRAU_PROF], prof[NOME_PROF], anoCompleto, prof[CURSO_PROF])
        try:
            cursor.execute(cmd)
        except:
            print "erro grau prof", prof[GRAU_PROF], prof[NOME_PROF]
            pass
        cursor.close()
        pass
    
    def preencherCatCursoProf(self, prof):
        CURSO_PROF = 3
        CATG_PROF = 4
        cursor = self.conn.cursor()
        cmd = """INSERT INTO Categoria(NomeCategoria) values ('{0}')""".format(prof[CATG_PROF])
        try:
            cursor.execute(cmd)
        except:
            print "erro categoria", prof[CATG_PROF]
        pass
        #inserir curso
        cmd = """INSERT INTO Curso(NomeCurso) values ('{0}')""".format(prof[CURSO_PROF])
        try:
            cursor.execute(cmd)
        except:
            print "erro curso", prof[CURSO_PROF]
        cursor.close()
        pass
        
    def preencherProfessor(self, lista):
        cursor = self.conn.cursor()
        NOME_PROF = 1
        CURSO_PROF = 3
        CATG_PROF = 4
        CODG_PORF = 0
      
        for prof in lista:
            self.preencherCatCursoProf(prof)
            #inserir prof
            cmd = """INSERT INTO Professor(NomeProfessor, CategoriaProfessor, 
                   CodigoProfessor) values ('{0}', 
                  '{1}', '{2}')""".format(prof[NOME_PROF], prof[CATG_PROF], prof[CODG_PORF])
            try:
                cursor.execute(cmd)
            except:
                print "erro prof", prof[1]
                pass
            self.preencherGrauProfessor( prof)
            self.preencherPorfEstab(prof)
        self.conn.commit()
        cursor.close()
        pass
        
    def preencherTipoAno(self, d_tipos, ano):
        cursor = self.conn.cursor()
        anoCompleto = ano + 2000 
        cmd = """INSERT INTO Ano(Ano) values ({0})""".format(anoCompleto)
        try:
            cursor.execute(cmd)
        except:
            print "erro ano"
            
        for key, value in d_tipos.iteritems():  
            cmd = """INSERT INTO TipoEstabelecimento(NomeTipoEstabelecimento) values ('{0}')""".format(key)
            try:
                cursor.execute(cmd)
            except:
                print "error tipo"
                
            cmd = """INSERT INTO TipoEstabelecimento_Ano(Ano, NomeTipoEstabelecimento) 
                    values ({0},'{1}')""".format(anoCompleto, key)
            try:
                cursor.execute(cmd)
            except:
                print "error tipo ano"
        self.conn.commit()
        cursor.close()
        pass
    
    def preencherEstabelecimentos(self,  d_estbelecimentos, tipo):
        cursor = self.conn.cursor()
         
        for key, value in d_estbelecimentos.iteritems():  
            cmd = """INSERT INTO Estabelecimento(NomeEstabelecimento, NomeTipoEstabelecimento) values ('{0}','{1}')""".format(key, tipo)
            try:
                cursor.execute(cmd)
            except:
                print "error estabelecimento"
                
        self.conn.commit()
        cursor.close()
        pass
        
    def preencherPorfEstab(self, prof):
        cursor = self.conn.cursor()
        NOME_PROF = 1
        ESTB_PROF = 8
        ANO = 6
        anoCompleto = 2000 + prof[ANO]
        cmd = """INSERT INTO ProfessorEstabelecimento(NomeEstabelecimento, NomeProfessor, Ano) 
                values ('{0}','{1}',{2})""".format(prof[ESTB_PROF], prof[NOME_PROF], anoCompleto)
        try:
            cursor.execute(cmd)
        except:
            print "erro prof estabelecimento"
        cursor.close()
        pass
    
    def consultarProfAno(self):
        cursor = self.conn.cursor()
        d = {}
        for ano in range(2000, 2010):
            cmd = """SELECT  COUNT (DISTINCT NomeProfessor) FROM ProfessorEstabelecimento WHERE Ano = {0}""".format(ano)
            cursor.execute(cmd)
            r = cursor.fetchone()
            d[ano] = r[0]
        cursor.close()
        return d
    
    def consultarProfTipoAno(self):
        cursor = self.conn.cursor()
        d = {}
        for ano in range(2000, 2010):
            cmd = """SELECT DISTINCT NomeTipoEstabelecimento FROM TipoEstabelecimento_Ano WHERE Ano = {0}""".format(ano)
            cursor.execute(cmd)
            r = cursor.fetchall()
            dTp = {}
            for x in r:
                counter = 0
                x[0].encode('utf-8')
                cmd = """SELECT DISTINCT NomeEstabelecimento FROM Estabelecimento WHERE NomeTipoEstabelecimento = '{0}' """.format(x[0])
                cursor.execute(cmd)
                c = cursor.fetchall()
                for y in c:
                    y[0].encode('utf-8')
                    cmd = """SELECT COUNT ( DISTINCT NomeProfessor) FROM ProfessorEstabelecimento WHERE NomeEstabelecimento = '{0}'and Ano = {1} """.format(y[0], ano)
                    cursor.execute(cmd)
                    c = cursor.fetchone()
                    counter += c[0]
                dTp[x[0]] = counter
            d[ano] = dTp
        return d
    
    def listaTipoEstabelecimentosAno(self):
        cursor = self.conn.cursor()
        d = {}
        for ano in range(2000, 2010):
            cmd = """ SELECT Distinct NomeTipoEstabelecimento FROM TipoEstabelecimento_Ano WHERE Ano = {0}""".format(ano)
            cursor.execute(cmd)
            r = cursor.fetchall()
            listaAno = []
            for x in r:
                x[0].encode('utf-8')
                listaAno.append(x[0])
            d[ano] = listaAno
        return d
    # retorna estabs com 0 profs
    def consultarProfEstabelecimentoAno(self):
        cursor = self.conn.cursor()
        d = {}
        for ano in range(2000, 2010):
            dEst = {}
            cmd = """SELECT DISTINCT NomeTipoEstabelecimento FROM TipoEstabelecimento_Ano WHERE Ano = {0}""".format(ano)
            cursor.execute(cmd)
            r = cursor.fetchall()
            for x in r:
                x[0].encode('utf-8')
                cmd = """SELECT DISTINCT NomeEstabelecimento FROM Estabelecimento WHERE NomeTipoEstabelecimento = '{0}' """.format(x[0])
                cursor.execute(cmd)
                c = cursor.fetchall()
                for y in c:
                    y[0].encode('utf-8')
                    cmd = """SELECT COUNT ( DISTINCT NomeProfessor) FROM ProfessorEstabelecimento WHERE NomeEstabelecimento = '{0}'and Ano = {1} """.format(y[0], ano)
                    cursor.execute(cmd)
                    c = cursor.fetchone()
                    dEst[y[0]] = c[0]
            d[ano] = dEst
        return d
        
    def consultarProfessorGrauAno (self):       
        cursor = self.conn.cursor()
        d = {}
        for ano in range(2000, 2010):
            dGrau = {}
            cmd = """SELECT DISTINCT NomeGrau FROM GrauProfessor WHERE Ano = {0}""".format(ano)
            cursor.execute(cmd)
            r = cursor.fetchall()
            for x in r:
                x[0].encode('utf-8')  
                cmd = """Select  count (Distinct NomeProfessor) FROM GrauProfessor WHERE NomeGrau = '{0}' and Ano = {1}""".format(x[0], ano)
                cursor.execute(cmd)
                g = cursor.fetchone()
                dGrau[x[0]] = g[0]
            d[ano] = dGrau
        return d 
    
    
    def consultaProfessorGrauEstabelecimentoAno(self):
        
        cursor = self.conn.cursor()
        d = {}
        cmd = """CREATE TEMPORARY VIEW IF NOT EXISTS ProfessorGrauEstabelecimentoAno
                AS 
                SELECT NomeGrau,  NomeEstabelecimento, ProfessorEstabelecimento.Ano
                FROM ProfessorEstabelecimento
                JOIN GrauProfessor
                ON ProfessorEstabelecimento.NomeProfessor = GrauProfessor.NomeProfessor 
                and ProfessorEstabelecimento.Ano = GrauProfessor.Ano
                """
        cursor.execute(cmd)
        cmd = """CREATE TEMPORARY VIEW IF NOT EXISTS EstabelecimentoAno
                AS 
                SELECT DISTINCT NomeEstabelecimento, Ano
                FROM ProfessorEstabelecimento
                """
        cursor.execute(cmd)
        
        for ano in range(2000, 2010):
            
            cmd = """SELECT DISTINCT NomeEstabelecimento FROM EstabelecimentoAno WHERE Ano = {0}""".format(ano)
            cursor.execute(cmd)
            le = cursor.fetchall()
            for e in le:
                dEstb = {}
                e[0].encode('utf-8')
                cmd = """SELECT DISTINCT NomeGrau FROM ProfessorGrauEstabelecimentoAno WHERE Ano = {0} 
                    and NomeEstabelecimento = '{1}'""".format(ano, e[0])
                cursor.execute(cmd)
                lg = cursor.fetchall()
                
                for g in lg:
                    g[0].encode('utf-8')
                    cmd="""SELECT COUNT (NomeGrau) FROM ProfessorGrauEstabelecimentoAno WHERE Ano = {0} 
                        AND NomeEstabelecimento = '{1}' AND NomeGrau = '{2}'""".format(ano, e[0], g[0])
                    cursor.execute(cmd)
                    tg = cursor.fetchone()
                    dEstb[g[0]]=tg[0]
               
            d[ano]=dEstb
            
        return d
                        
         
    def listaProfGrauAno(self):
        cursor = self.conn.cursor()
        dicAno = {}
        for ano in range(2000, 2010):
            
            cmd = """SELECT DISTINCT NomeGrau FROM GrauProfessor WHERE Ano = {0}""".format(ano)
            cursor.execute(cmd)
            lg = cursor.fetchall()
            dicGrau = {}
            for g in lg: 
                listaGrau = []
                g[0].encode('utf-8')
                cmd = """ SELECT NomeProfessor FROM GrauProfessor WHERE Ano = {0} AND NomeGrau = '{1}' """.format(ano, g[0])
                cursor.execute(cmd)
                lp = cursor.fetchall()
                for p in lp:
                    p[0].encode('utf-8')
                    listaGrau.append(p[0])
                dicGrau[g[0]] = listaGrau    
            dicAno[ano] = dicGrau
            
        return dicAno
    
    def listaEstbInstAno(self):
        cursor = self.conn.cursor()
        d = {}
        for ano in range(2000, 2010):
            cmd = """SELECT DISTINCT NomeTipoEstabelecimento FROM TipoEstabelecimento_Ano WHERE Ano = {0}""".format(ano)
            cursor.execute(cmd)
            r = cursor.fetchall()
            
            for x in r:
                dTp = {}
                lEst = []
                x[0].encode('utf-8')
                cmd = """SELECT DISTINCT NomeEstabelecimento FROM Estabelecimento WHERE NomeTipoEstabelecimento = '{0}' """.format(x[0])
                cursor.execute(cmd)
                c = cursor.fetchall()
                for y in c:
                    y[0].encode('utf-8')
                    cmd = """SELECT  DISTINCT NomeEstabelecimento FROM ProfessorEstabelecimento WHERE NomeEstabelecimento = '{0}'and Ano = {1} """.format(y[0], ano)
                    cursor.execute(cmd)
                    c = cursor.fetchall()
                    for e in c:
                        e[0].encode('utf-8')
                        lEst.append(e[0])
            dTp[x[0]] = lEst
            d[ano] = dTp
        return d
    